from django.shortcuts import render, get_object_or_404
from django.utils import timezone
from django.db import models
from rest_framework import status, viewsets
from rest_framework.decorators import api_view, permission_classes, action
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response
from rest_framework.pagination import PageNumberPagination
from .models import DatabaseInstance, SQLRequest, SQLQueryHistory, SQLAuditLog
from .database_manager import DatabaseManager, DatabaseConnectionError, SQLExecutionError
from .serializers import (
    DatabaseInstanceSerializer, SQLRequestSerializer, SQLRequestCreateSerializer,
    SQLRequestReviewSerializer, SQLQueryHistorySerializer, SQLAuditLogSerializer,
    AdminUserSerializer
)
from user_management.models import CustomUser
import json
import logging

logger = logging.getLogger(__name__)


class StandardResultsSetPagination(PageNumberPagination):
    page_size = 20
    page_size_query_param = 'page_size'
    max_page_size = 100


@api_view(['GET', 'POST'])
@permission_classes([IsAuthenticated])
def database_instances(request):
    """数据库实例管理"""
    if request.method == 'GET':
        instances = DatabaseInstance.objects.filter(is_active=True)

        # 分页
        paginator = StandardResultsSetPagination()
        page = paginator.paginate_queryset(instances, request)

        data = []
        for instance in page:
            data.append({
                'id': instance.id,
                'name': instance.name,
                'host': instance.host,
                'port': instance.port,
                'database_type': instance.database_type,
                'database_name': instance.database_name,
                'description': instance.description,
                'created_at': instance.created_at.isoformat(),
            })

        return paginator.get_paginated_response(data)

    elif request.method == 'POST':
        try:
            data = request.data
            instance = DatabaseInstance.objects.create(
                name=data.get('name'),
                host=data.get('host'),
                port=data.get('port', 3306),
                database_type=data.get('database_type', 'mysql'),
                username=data.get('username'),
                password=data.get('password'),
                database_name=data.get('database_name'),
                description=data.get('description', ''),
            )

            return Response({
                'message': '数据库实例创建成功',
                'id': instance.id
            }, status=status.HTTP_201_CREATED)

        except Exception as e:
            return Response({
                'error': f'创建失败: {str(e)}'
            }, status=status.HTTP_400_BAD_REQUEST)


@api_view(['DELETE', 'PUT', 'PATCH'])
@permission_classes([IsAuthenticated])
def delete_database_instance(request, instance_id):
    """删除或更新数据库实例"""
    try:
        instance = DatabaseInstance.objects.get(id=instance_id)

        if request.method == 'DELETE':
            instance.delete()
            return Response({
                'message': '数据库实例删除成功'
            })

        elif request.method in ['PUT', 'PATCH']:
            # 更新数据库实例
            data = request.data

            # 更新字段
            if 'name' in data:
                instance.name = data['name']
            if 'database_type' in data:
                instance.database_type = data['database_type']
            if 'host' in data:
                instance.host = data['host']
            if 'port' in data:
                instance.port = data['port']
            if 'database_name' in data:
                instance.database_name = data['database_name']
            if 'username' in data:
                instance.username = data['username']
            if 'password' in data:
                instance.password = data['password']
            if 'description' in data:
                instance.description = data['description']

            instance.updated_at = timezone.now()
            instance.save()

            return Response({
                'id': instance.id,
                'name': instance.name,
                'host': instance.host,
                'port': instance.port,
                'database_type': instance.database_type,
                'database_name': instance.database_name,
                'description': instance.description,
                'created_at': instance.created_at.isoformat(),
                'updated_at': instance.updated_at.isoformat(),
                'message': '数据库实例更新成功'
            })

    except DatabaseInstance.DoesNotExist:
        return Response({
            'error': '数据库实例不存在'
        }, status=status.HTTP_404_NOT_FOUND)
    except Exception as e:
        action = '删除' if request.method == 'DELETE' else '更新'
        return Response({
            'error': f'{action}失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def execute_query(request):
    """执行SQL查询"""
    try:
        data = request.data
        database_id = data.get('database_id')
        sql_content = data.get('sql_content', '').strip()
        limit = data.get('limit', 1000)  # 默认限制1000行

        if not database_id or not sql_content:
            return Response({
                'error': '数据库ID和SQL内容不能为空'
            }, status=status.HTTP_400_BAD_REQUEST)

        # 获取数据库实例
        try:
            db_instance = DatabaseInstance.objects.get(id=database_id, is_active=True)
        except DatabaseInstance.DoesNotExist:
            return Response({
                'error': '数据库实例不存在'
            }, status=status.HTTP_404_NOT_FOUND)

        # 获取客户端信息
        ip_address = request.META.get('HTTP_X_FORWARDED_FOR',
                                    request.META.get('REMOTE_ADDR', ''))
        user_agent = request.META.get('HTTP_USER_AGENT', '')

        try:
            # 使用数据库管理器执行查询
            result = DatabaseManager.execute_query(
                db_instance=db_instance,
                sql_content=sql_content,
                user=request.user,
                limit=limit
            )

            # 处理查询历史和审计日志
            if result.get('multiple_results'):
                # 多查询结果，为每个成功的查询记录历史
                for sub_result in result.get('results', []):
                    if sub_result.get('success'):
                        SQLQueryHistory.objects.create(
                            user=request.user,
                            database_instance=db_instance,
                            sql_content=sub_result.get('sql_content', ''),
                            execution_time=sub_result.get('execution_time', 0),
                            rows_affected=sub_result.get('affected_rows', sub_result.get('row_count', 0)),
                            is_success=True,
                            ip_address=ip_address,
                            user_agent=user_agent
                        )

                # 记录批量执行的审计日志
                SQLAuditLog.objects.create(
                    user=request.user,
                    action='batch_query_execute',
                    target_type='database_instance',
                    target_id=db_instance.id,
                    description=f'批量执行{result["total_statements"]}条SQL语句: {sql_content[:100]}{"..." if len(sql_content) > 100 else ""}',
                    ip_address=ip_address,
                    user_agent=user_agent
                )
            else:
                # 单查询结果
                SQLQueryHistory.objects.create(
                    user=request.user,
                    database_instance=db_instance,
                    sql_content=sql_content,
                    execution_time=result['execution_time'],
                    rows_affected=result.get('affected_rows', result.get('row_count', 0)),
                    is_success=True,
                    ip_address=ip_address,
                    user_agent=user_agent
                )

                # 记录审计日志
                SQLAuditLog.objects.create(
                    user=request.user,
                    action='query_execute',
                    target_type='database_instance',
                    target_id=db_instance.id,
                    description=f'执行{result.get("sql_type", "SQL")}: {sql_content[:100]}{"..." if len(sql_content) > 100 else ""}',
                    ip_address=ip_address,
                    user_agent=user_agent
                )

            return Response({
                'message': result['message'],
                'result': result
            })

        except (DatabaseConnectionError, SQLExecutionError) as e:
            # 记录失败的查询历史
            SQLQueryHistory.objects.create(
                user=request.user,
                database_instance=db_instance,
                sql_content=sql_content,
                execution_time=0,
                rows_affected=0,
                is_success=False,
                error_message=str(e),
                ip_address=ip_address,
                user_agent=user_agent
            )

            logger.error(f"SQL执行失败 - 用户: {request.user.username}, 数据库: {db_instance.name}, 错误: {str(e)}")

            return Response({
                'error': str(e)
            }, status=status.HTTP_400_BAD_REQUEST)

    except Exception as e:
        logger.error(f"SQL查询接口异常: {str(e)}")
        return Response({
            'error': f'查询执行失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['GET', 'POST'])
@permission_classes([IsAuthenticated])
def sql_requests(request):
    """SQL变更请求管理"""
    if request.method == 'GET':
        requests_qs = SQLRequest.objects.all()

        # 过滤条件
        status_filter = request.GET.get('status')
        if status_filter:
            requests_qs = requests_qs.filter(status=status_filter)

        # 分页
        paginator = StandardResultsSetPagination()
        page = paginator.paginate_queryset(requests_qs, request)

        data = []
        for req in page:
            data.append({
                'id': req.id,
                'title': req.title,
                'description': req.description,
                'request_type': req.request_type,
                'status': req.status,
                'database_instance': {
                    'id': req.database_instance.id,
                    'name': req.database_instance.name
                },
                'requester': {
                    'id': req.requester.id,
                    'username': req.requester.username
                },
                'reviewer': {
                    'id': req.reviewer.id,
                    'username': req.reviewer.username
                } if req.reviewer else None,
                'created_at': req.created_at.isoformat(),
                'reviewed_at': req.reviewed_at.isoformat() if req.reviewed_at else None,
            })

        return paginator.get_paginated_response(data)

    elif request.method == 'POST':
        try:
            data = request.data

            # 获取数据库实例
            try:
                db_instance = DatabaseInstance.objects.get(
                    id=data.get('database_id'),
                    is_active=True
                )
            except DatabaseInstance.DoesNotExist:
                return Response({
                    'error': '数据库实例不存在'
                }, status=status.HTTP_404_NOT_FOUND)

            sql_request = SQLRequest.objects.create(
                title=data.get('title'),
                description=data.get('description'),
                sql_content=data.get('sql_content'),
                request_type=data.get('request_type', 'query'),
                database_instance=db_instance,
                requester=request.user
            )

            # 记录审计日志
            ip_address = request.META.get('HTTP_X_FORWARDED_FOR',
                                        request.META.get('REMOTE_ADDR', ''))
            user_agent = request.META.get('HTTP_USER_AGENT', '')

            SQLAuditLog.objects.create(
                user=request.user,
                action='create_request',
                target_type='sql_request',
                target_id=sql_request.id,
                description=f'创建SQL变更请求: {sql_request.title}',
                ip_address=ip_address,
                user_agent=user_agent
            )

            return Response({
                'message': 'SQL变更请求创建成功',
                'id': sql_request.id
            }, status=status.HTTP_201_CREATED)

        except Exception as e:
            return Response({
                'error': f'创建失败: {str(e)}'
            }, status=status.HTTP_400_BAD_REQUEST)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def review_sql_request(request, request_id):
    """审核SQL变更请求"""
    try:
        sql_request = SQLRequest.objects.get(id=request_id)
    except SQLRequest.DoesNotExist:
        return Response({
            'error': 'SQL请求不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    if sql_request.status != 'pending':
        return Response({
            'error': '该请求已被审核'
        }, status=status.HTTP_400_BAD_REQUEST)

    try:
        data = request.data
        action = data.get('action')  # 'approve' or 'reject'
        comment = data.get('comment', '')

        if action == 'approve':
            sql_request.status = 'approved'
        elif action == 'reject':
            sql_request.status = 'rejected'
        else:
            return Response({
                'error': '无效的审核操作'
            }, status=status.HTTP_400_BAD_REQUEST)

        sql_request.reviewer = request.user
        sql_request.review_comment = comment
        sql_request.reviewed_at = timezone.now()
        sql_request.save()

        # 记录审计日志
        ip_address = request.META.get('HTTP_X_FORWARDED_FOR',
                                    request.META.get('REMOTE_ADDR', ''))
        user_agent = request.META.get('HTTP_USER_AGENT', '')

        SQLAuditLog.objects.create(
            user=request.user,
            action='review_request',
            target_type='sql_request',
            target_id=sql_request.id,
            description=f'审核SQL请求: {action} - {comment}',
            ip_address=ip_address,
            user_agent=user_agent
        )

        return Response({
            'message': f'SQL请求已{sql_request.get_status_display()}'
        })

    except Exception as e:
        return Response({
            'error': f'审核失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def sql_history(request):
    """SQL历史记录"""
    history_qs = SQLQueryHistory.objects.all()

    # 过滤条件
    user_id = request.GET.get('user_id')
    if user_id:
        history_qs = history_qs.filter(user_id=user_id)

    database_id = request.GET.get('database_id')
    if database_id:
        history_qs = history_qs.filter(database_instance_id=database_id)

    # 分页
    paginator = StandardResultsSetPagination()
    page = paginator.paginate_queryset(history_qs, request)

    data = []
    for history in page:
        data.append({
            'id': history.id,
            'user': {
                'id': history.user.id,
                'username': history.user.username
            },
            'database_instance': {
                'id': history.database_instance.id,
                'name': history.database_instance.name
            },
            'sql_content': history.sql_content,
            'execution_time': history.execution_time,
            'rows_affected': history.rows_affected,
            'is_success': history.is_success,
            'error_message': history.error_message,
            'ip_address': history.ip_address,
            'created_at': history.created_at.isoformat(),
        })

    return paginator.get_paginated_response(data)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def my_tickets(request):
    """我的工单列表"""
    # 只显示当前用户提交的工单
    tickets_qs = SQLRequest.objects.filter(requester=request.user)

    # 状态过滤
    status_filter = request.GET.get('status')
    if status_filter:
        tickets_qs = tickets_qs.filter(status=status_filter)

    # 优先级过滤
    priority_filter = request.GET.get('priority')
    if priority_filter:
        tickets_qs = tickets_qs.filter(priority=priority_filter)

    # 请求类型过滤
    request_type_filter = request.GET.get('request_type')
    if request_type_filter:
        tickets_qs = tickets_qs.filter(request_type=request_type_filter)

    # 搜索
    search = request.GET.get('search')
    if search:
        tickets_qs = tickets_qs.filter(
            models.Q(title__icontains=search) |
            models.Q(description__icontains=search)
        )

    # 排序
    order_by = request.GET.get('order_by', '-created_at')
    tickets_qs = tickets_qs.order_by(order_by)

    # 分页
    paginator = StandardResultsSetPagination()
    page = paginator.paginate_queryset(tickets_qs, request)

    data = []
    for ticket in page:
        data.append({
            'id': ticket.id,
            'title': ticket.title,
            'description': ticket.description,
            'request_type': ticket.request_type,
            'request_type_display': ticket.get_request_type_display(),
            'status': ticket.status,
            'status_display': ticket.get_status_display(),
            'priority': ticket.priority,
            'priority_display': ticket.get_priority_display(),
            'database_instance': {
                'id': ticket.database_instance.id,
                'name': ticket.database_instance.name,
                'database_type': ticket.database_instance.database_type,
            },
            'reviewer': {
                'id': ticket.reviewer.id,
                'username': ticket.reviewer.username,
                'first_name': ticket.reviewer.first_name,
                'last_name': ticket.reviewer.last_name,
            } if ticket.reviewer else None,
            'executor': {
                'id': ticket.executor.id,
                'username': ticket.executor.username,
                'first_name': ticket.executor.first_name,
                'last_name': ticket.executor.last_name,
            } if ticket.executor else None,
            'review_comment': ticket.review_comment,
            'execution_result': ticket.execution_result,
            'expected_execution_time': ticket.expected_execution_time.isoformat() if ticket.expected_execution_time else None,
            'created_at': ticket.created_at.isoformat(),
            'reviewed_at': ticket.reviewed_at.isoformat() if ticket.reviewed_at else None,
            'executed_at': ticket.executed_at.isoformat() if ticket.executed_at else None,
            'withdrawn_at': ticket.withdrawn_at.isoformat() if ticket.withdrawn_at else None,
        })

    return paginator.get_paginated_response(data)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def my_ticket_detail(request, ticket_id):
    """我的工单详情"""
    try:
        ticket = SQLRequest.objects.get(id=ticket_id, requester=request.user)
    except SQLRequest.DoesNotExist:
        return Response({
            'error': '工单不存在或无权限访问'
        }, status=status.HTTP_404_NOT_FOUND)

    data = {
        'id': ticket.id,
        'title': ticket.title,
        'description': ticket.description,
        'sql_content': ticket.sql_content,
        'request_type': ticket.request_type,
        'request_type_display': ticket.get_request_type_display(),
        'status': ticket.status,
        'status_display': ticket.get_status_display(),
        'priority': ticket.priority,
        'priority_display': ticket.get_priority_display(),
        'database_instance': {
            'id': ticket.database_instance.id,
            'name': ticket.database_instance.name,
            'host': ticket.database_instance.host,
            'port': ticket.database_instance.port,
            'database_type': ticket.database_instance.database_type,
            'database_name': ticket.database_instance.database_name,
        },
        'requester': {
            'id': ticket.requester.id,
            'username': ticket.requester.username,
            'first_name': ticket.requester.first_name,
            'last_name': ticket.requester.last_name,
            'email': ticket.requester.email,
        },
        'reviewer': {
            'id': ticket.reviewer.id,
            'username': ticket.reviewer.username,
            'first_name': ticket.reviewer.first_name,
            'last_name': ticket.reviewer.last_name,
            'email': ticket.reviewer.email,
        } if ticket.reviewer else None,
        'executor': {
            'id': ticket.executor.id,
            'username': ticket.executor.username,
            'first_name': ticket.executor.first_name,
            'last_name': ticket.executor.last_name,
            'email': ticket.executor.email,
        } if ticket.executor else None,
        'review_comment': ticket.review_comment,
        'execution_result': ticket.execution_result,
        'expected_execution_time': ticket.expected_execution_time.isoformat() if ticket.expected_execution_time else None,
        'created_at': ticket.created_at.isoformat(),
        'reviewed_at': ticket.reviewed_at.isoformat() if ticket.reviewed_at else None,
        'executed_at': ticket.executed_at.isoformat() if ticket.executed_at else None,
        'withdrawn_at': ticket.withdrawn_at.isoformat() if ticket.withdrawn_at else None,
        # 是否可以撤回（只有待审核状态可以撤回）
        'can_withdraw': ticket.status == 'pending',
    }

    # 获取工单相关的操作历史
    audit_logs = SQLAuditLog.objects.filter(
        target_type='sql_request',
        target_id=ticket.id
    ).order_by('-created_at')

    data['operation_history'] = []
    for log in audit_logs:
        data['operation_history'].append({
            'id': log.id,
            'action': log.action,
            'action_display': log.get_action_display(),
            'description': log.description,
            'user': {
                'id': log.user.id,
                'username': log.user.username,
                'first_name': log.user.first_name,
                'last_name': log.user.last_name,
            },
            'ip_address': log.ip_address,
            'created_at': log.created_at.isoformat(),
        })

    return Response(data)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def withdraw_ticket(request, ticket_id):
    """撤回工单"""
    try:
        ticket = SQLRequest.objects.get(id=ticket_id, requester=request.user)
    except SQLRequest.DoesNotExist:
        return Response({
            'error': '工单不存在或无权限访问'
        }, status=status.HTTP_404_NOT_FOUND)

    # 只有待审核状态的工单可以撤回
    if ticket.status != 'pending':
        return Response({
            'error': '只有待审核状态的工单可以撤回'
        }, status=status.HTTP_400_BAD_REQUEST)

    try:
        # 更新工单状态
        ticket.status = 'withdrawn'
        ticket.withdrawn_at = timezone.now()
        ticket.save()

        # 记录审计日志
        ip_address = request.META.get('HTTP_X_FORWARDED_FOR',
                                    request.META.get('REMOTE_ADDR', ''))
        user_agent = request.META.get('HTTP_USER_AGENT', '')

        SQLAuditLog.objects.create(
            user=request.user,
            action='withdraw_request',
            target_type='sql_request',
            target_id=ticket.id,
            description=f'撤回SQL工单: {ticket.title}',
            ip_address=ip_address,
            user_agent=user_agent
        )

        return Response({
            'message': '工单撤回成功'
        })

    except Exception as e:
        return Response({
            'error': f'撤回失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def test_database_connection(request, instance_id):
    """测试数据库连接"""
    try:
        db_instance = DatabaseInstance.objects.get(id=instance_id, is_active=True)
    except DatabaseInstance.DoesNotExist:
        return Response({
            'error': '数据库实例不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    try:
        result = DatabaseManager.test_connection(db_instance)
        return Response(result)
    except Exception as e:
        logger.error(f"测试数据库连接失败: {str(e)}")
        return Response({
            'success': False,
            'message': f'连接测试失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def test_connection_params(request):
    """测试数据库连接参数（不保存实例）"""
    try:
        data = request.data

        # 验证必要参数
        required_fields = ['host', 'port', 'database_type', 'username', 'password']
        for field in required_fields:
            if field not in data:
                return Response({
                    'success': False,
                    'message': f'缺少必要参数: {field}'
                }, status=status.HTTP_400_BAD_REQUEST)

        # 创建临时数据库实例对象（不保存到数据库）
        class TempDatabaseInstance:
            def __init__(self, data):
                from django.utils import timezone
                self.id = 'temp_test'  # 添加id属性
                self.name = data.get('name', 'temp_test')
                self.host = data.get('host')
                self.port = int(data.get('port', 3306))
                self.database_type = data.get('database_type', 'mysql')
                self.username = data.get('username')
                self.password = data.get('password')
                self.database_name = data.get('database_name', '')
                self.updated_at = timezone.now()  # 添加updated_at属性

        temp_instance = TempDatabaseInstance(data)

        # 测试连接
        result = DatabaseManager.test_connection(temp_instance)
        return Response(result)

    except Exception as e:
        logger.error(f"测试连接参数失败: {str(e)}")
        return Response({
            'success': False,
            'message': f'连接测试失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def get_database_schema(request, instance_id):
    """获取数据库结构"""
    try:
        db_instance = DatabaseInstance.objects.get(id=instance_id, is_active=True)
    except DatabaseInstance.DoesNotExist:
        return Response({
            'error': '数据库实例不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    try:
        schema_info = DatabaseManager.get_database_schema(db_instance)
        return Response(schema_info)
    except DatabaseConnectionError as e:
        return Response({
            'error': str(e)
        }, status=status.HTTP_400_BAD_REQUEST)
    except Exception as e:
        logger.error(f"获取数据库结构失败: {str(e)}")
        return Response({
            'error': f'获取数据库结构失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['POST'])
@permission_classes([IsAuthenticated])
def analyze_sql(request):
    """分析SQL语句"""
    try:
        sql_content = request.data.get('sql_content', '').strip()
        if not sql_content:
            return Response({
                'error': 'SQL内容不能为空'
            }, status=status.HTTP_400_BAD_REQUEST)

        analysis = DatabaseManager.analyze_sql_type(sql_content)
        return Response(analysis)

    except Exception as e:
        logger.error(f"SQL分析失败: {str(e)}")
        return Response({
            'error': f'SQL分析失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def get_database_list(request, instance_id):
    """获取数据库实例下的数据库列表"""
    try:
        db_instance = DatabaseInstance.objects.get(id=instance_id, is_active=True)
    except DatabaseInstance.DoesNotExist:
        return Response({
            'error': '数据库实例不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    try:
        # 获取是否包含系统数据库的参数
        include_system_dbs = request.GET.get('include_system_dbs', 'false').lower() == 'true'
        logger.info(f"获取数据库列表: instance_id={instance_id}, include_system_dbs={include_system_dbs}")

        database_list = DatabaseManager.get_database_list(db_instance, include_system_dbs)
        return Response(database_list)
    except DatabaseConnectionError as e:
        return Response({
            'error': str(e)
        }, status=status.HTTP_400_BAD_REQUEST)
    except Exception as e:
        logger.error(f"获取数据库列表失败: {str(e)}")
        return Response({
            'error': f'获取数据库列表失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def get_table_list(request, instance_id, database):
    """获取指定数据库的表列表"""
    logger.info(f"获取表列表请求: instance_id={instance_id}, database={database}")

    try:
        db_instance = DatabaseInstance.objects.get(id=instance_id, is_active=True)
        logger.info(f"找到数据库实例: {db_instance.name}, database_name={db_instance.database_name}")
    except DatabaseInstance.DoesNotExist:
        logger.error(f"数据库实例不存在: {instance_id}")
        return Response({
            'error': '数据库实例不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    try:
        logger.info(f"调用DatabaseManager.get_table_list: db_instance={db_instance.name}, database={database}")
        table_list = DatabaseManager.get_table_list(db_instance, database)
        logger.info(f"成功获取表列表: {len(table_list.get('tables', []))} 个表")
        return Response(table_list)
    except DatabaseConnectionError as e:
        logger.error(f"数据库连接错误: {str(e)}")
        return Response({
            'error': str(e)
        }, status=status.HTTP_400_BAD_REQUEST)
    except Exception as e:
        import traceback
        logger.error(f"获取表列表失败: {str(e)}")
        logger.error(f"完整错误堆栈: {traceback.format_exc()}")
        return Response({
            'error': f'获取表列表失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def get_table_structure(request, instance_id, database, table):
    """获取表结构"""
    try:
        db_instance = DatabaseInstance.objects.get(id=instance_id, is_active=True)
    except DatabaseInstance.DoesNotExist:
        return Response({
            'error': '数据库实例不存在'
        }, status=status.HTTP_404_NOT_FOUND)

    try:
        table_structure = DatabaseManager.get_table_structure(db_instance, database, table)
        return Response(table_structure)
    except DatabaseConnectionError as e:
        return Response({
            'error': str(e)
        }, status=status.HTTP_400_BAD_REQUEST)
    except Exception as e:
        logger.error(f"获取表结构失败: {str(e)}")
        return Response({
            'error': f'获取表结构失败: {str(e)}'
        }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


class SQLRequestViewSet(viewsets.ModelViewSet):
    """SQL工单管理视图集"""
    queryset = SQLRequest.objects.all()
    permission_classes = [IsAuthenticated]
    pagination_class = StandardResultsSetPagination

    def get_serializer_class(self):
        if self.action == 'create':
            return SQLRequestCreateSerializer
        elif self.action == 'review':
            return SQLRequestReviewSerializer
        return SQLRequestSerializer

    def get_queryset(self):
        queryset = SQLRequest.objects.select_related(
            'requester', 'reviewer', 'executor', 'database_instance'
        ).all()
        user = self.request.user

        # 检查是否是"我的工单"请求
        my_orders = self.request.query_params.get('my_orders')
        if my_orders:
            # 只返回当前用户的工单
            queryset = queryset.filter(requester=user)
        else:
            # 根据用户角色过滤
            if not (user.is_superuser or user.is_staff):
                # 普通用户只能看到自己的工单
                queryset = queryset.filter(requester=user)

        # 状态过滤
        status_filter = self.request.query_params.get('status')
        if status_filter:
            queryset = queryset.filter(status=status_filter)

        # 申请人过滤
        requester_filter = self.request.query_params.get('requester')
        if requester_filter:
            queryset = queryset.filter(requester_id=requester_filter)

        # 审核人过滤
        reviewer_filter = self.request.query_params.get('reviewer')
        if reviewer_filter:
            queryset = queryset.filter(reviewer_id=reviewer_filter)

        return queryset.order_by('-created_at')

    @action(detail=True, methods=['post'])
    def review(self, request, pk=None):
        """审核工单"""
        work_order = self.get_object()

        # 检查权限：只有管理员可以审核
        if not (request.user.is_superuser or request.user.is_staff):
            return Response({
                'error': '权限不足，只有管理员可以审核工单'
            }, status=status.HTTP_403_FORBIDDEN)

        # 检查工单状态
        if work_order.status != 'pending':
            return Response({
                'error': '只能审核待审核状态的工单'
            }, status=status.HTTP_400_BAD_REQUEST)

        serializer = SQLRequestReviewSerializer(
            work_order,
            data=request.data,
            context={'request': request}
        )

        if serializer.is_valid():
            serializer.save()

            # 记录审计日志
            SQLAuditLog.objects.create(
                user=request.user,
                action='review_request',
                target_type='SQLRequest',
                target_id=work_order.id,
                description=f"审核工单: {work_order.title} - {work_order.get_status_display()}",
                ip_address=get_client_ip(request),
                user_agent=request.META.get('HTTP_USER_AGENT', '')
            )

            return Response(SQLRequestSerializer(work_order).data)

        return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)

    def _is_dangerous_sql(self, sql_content):
        """检测危险SQL"""
        dangerous_keywords = [
            'DROP', 'DELETE', 'TRUNCATE', 'ALTER', 'CREATE', 'UPDATE', 'INSERT', 'GRANT', 'REVOKE'
        ]
        sql_upper = sql_content.upper().strip()

        for keyword in dangerous_keywords:
            if sql_upper.startswith(keyword):
                return True, keyword

        return False, None

    @action(detail=True, methods=['post'])
    def execute(self, request, pk=None):
        """执行工单SQL"""
        work_order = self.get_object()

        # 检查权限：用户只能执行自己的工单，管理员可以执行任何工单
        if not (request.user.is_superuser or request.user.is_staff or work_order.requester == request.user):
            return Response({
                'error': '权限不足，您只能执行自己提交的工单'
            }, status=status.HTTP_403_FORBIDDEN)

        # 检查工单状态
        if work_order.status != 'approved':
            return Response({
                'error': '只能执行已批准的工单'
            }, status=status.HTTP_400_BAD_REQUEST)

        # 检查危险SQL
        is_dangerous, dangerous_keyword = self._is_dangerous_sql(work_order.sql_content)
        confirm_dangerous = request.data.get('confirm_dangerous', False)

        if is_dangerous and not confirm_dangerous:
            return Response({
                'error': 'dangerous_sql',
                'message': f'检测到危险SQL操作：{dangerous_keyword}',
                'dangerous_keyword': dangerous_keyword,
                'require_confirmation': True
            }, status=status.HTTP_400_BAD_REQUEST)

        try:
            # 更新状态为执行中
            work_order.status = 'executing'
            work_order.executor = request.user
            work_order.save()

            # 执行SQL
            db_instance = work_order.database_instance
            result = DatabaseManager.execute_query(
                db_instance=db_instance,
                sql_content=work_order.sql_content,
                user=request.user,
                limit=10000  # 工单执行不限制返回行数
            )

            # 更新执行结果
            work_order.status = 'executed'
            work_order.executed_at = timezone.now()
            work_order.execution_result = json.dumps(result, ensure_ascii=False)
            work_order.save()

            # 记录审计日志
            SQLAuditLog.objects.create(
                user=request.user,
                action='execute_request',
                target_type='SQLRequest',
                target_id=work_order.id,
                description=f"执行工单: {work_order.title}" + (f" (危险操作: {dangerous_keyword})" if is_dangerous else ""),
                ip_address=get_client_ip(request),
                user_agent=request.META.get('HTTP_USER_AGENT', '')
            )

            return Response({
                'message': '工单执行成功',
                'result': result,
                'is_dangerous': is_dangerous
            })

        except Exception as e:
            # 更新状态为执行失败
            work_order.status = 'failed'
            work_order.execution_result = str(e)
            work_order.save()

            logger.error(f"工单执行失败: {str(e)}")
            return Response({
                'error': f'工单执行失败: {str(e)}'
            }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    @action(detail=True, methods=['post'])
    def withdraw(self, request, pk=None):
        """撤回工单"""
        work_order = self.get_object()

        # 检查权限：只有申请人可以撤回自己的工单
        if work_order.requester != request.user:
            return Response({
                'error': '只能撤回自己提交的工单'
            }, status=status.HTTP_403_FORBIDDEN)

        # 检查工单状态
        if work_order.status not in ['pending', 'draft']:
            return Response({
                'error': '只能撤回待审核或草稿状态的工单'
            }, status=status.HTTP_400_BAD_REQUEST)

        work_order.status = 'withdrawn'
        work_order.withdrawn_at = timezone.now()
        work_order.save()

        # 记录审计日志
        SQLAuditLog.objects.create(
            user=request.user,
            action='withdraw_request',
            target_type='SQLRequest',
            target_id=work_order.id,
            description=f"撤回工单: {work_order.title}",
            ip_address=get_client_ip(request),
            user_agent=request.META.get('HTTP_USER_AGENT', '')
        )

        return Response({
            'message': '工单撤回成功'
        })


@api_view(['GET'])
@permission_classes([IsAuthenticated])
def get_admin_users(request):
    """获取管理员用户列表（用于工单审核人选择）"""
    current_user = request.user

    # 获取当前用户所属的用户组
    user_groups = current_user.user_groups.all()

    if user_groups.exists():
        # 用户在组内，只返回组内有审核权限的用户
        group_user_ids = []
        for group in user_groups:
            # 获取组内所有用户
            group_users = group.customuser_set.filter(is_active=True)
            # 筛选有审核权限的用户（管理员或员工）
            group_admins = group_users.filter(
                models.Q(is_superuser=True) | models.Q(is_staff=True)
            )
            group_user_ids.extend(group_admins.values_list('id', flat=True))

        # 去重并获取用户对象
        admin_users = CustomUser.objects.filter(
            id__in=set(group_user_ids),
            is_active=True
        ).distinct()

        # 添加当前用户组信息到响应
        user_in_group = True
        group_names = [group.name for group in user_groups]
    else:
        # 用户不在任何组内，返回所有有审核权限的用户
        admin_users = CustomUser.objects.filter(
            models.Q(is_superuser=True) | models.Q(is_staff=True),
            is_active=True
        )
        user_in_group = False
        group_names = []

    serializer = AdminUserSerializer(admin_users, many=True)
    return Response({
        'reviewers': serializer.data,
        'user_in_group': user_in_group,
        'group_names': group_names,
        'total_count': admin_users.count()
    })


def get_client_ip(request):
    """获取客户端IP地址"""
    x_forwarded_for = request.META.get('HTTP_X_FORWARDED_FOR')
    if x_forwarded_for:
        ip = x_forwarded_for.split(',')[0]
    else:
        ip = request.META.get('REMOTE_ADDR')
    return ip
